package com.sz.admin.datainfo.service.impl;

import cn.dev33.satoken.stp.StpUtil;
import com.mybatisflex.core.paginate.Page;
import com.mybatisflex.core.query.QueryWrapper;
import com.mybatisflex.core.row.Row;
import com.sz.admin.datainfo.mapper.DataStatisticsMapper;
import com.sz.admin.datainfo.pojo.dto.DataStatisticsQueryDTO;
import com.sz.admin.datainfo.pojo.vo.DriverStatisticsVO;
import com.sz.admin.datainfo.pojo.vo.VehicleStatisticsVO;
import com.sz.admin.datainfo.pojo.vo.WorkDataStatisticsVO;
import com.sz.admin.datainfo.pojo.vo.WorkDataGroupedByDateVO;
import com.sz.admin.datainfo.service.DataStatisticsService;
import com.sz.admin.system.pojo.po.SysUser;
import com.sz.admin.system.service.SysUserService;
import com.sz.admin.vehicleinfo.pojo.po.VehicleInfo;
import com.sz.admin.vehicleinfo.service.VehicleInfoService;
import com.sz.core.common.entity.LoginUser;
import com.sz.core.common.entity.PageResult;
import com.sz.core.datascope.SimpleDataScopeHelper;
import com.sz.core.util.PageUtils;
import com.sz.security.core.util.LoginUtils;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

import static com.mybatisflex.core.query.QueryMethods.*;
import static com.sz.admin.system.pojo.po.table.SysDeptTableDef.SYS_DEPT;
import static com.sz.admin.system.pojo.po.table.SysUserDeptTableDef.SYS_USER_DEPT;
import static com.sz.admin.system.pojo.po.table.SysUserRoleTableDef.SYS_USER_ROLE;
import static com.sz.admin.system.pojo.po.table.SysUserTableDef.SYS_USER;
import static com.sz.admin.vehicleinfo.pojo.po.table.VehicleInfoTableDef.VEHICLE_INFO;

/**
 * <p>
 * 数据统计 服务实现类
 * </p>
 *
 * @author sz-admin
 * @since 2025-09-24
 */
@Service
@RequiredArgsConstructor
public class DataStatisticsServiceImpl implements DataStatisticsService {

    private final DataStatisticsMapper dataStatisticsMapper;
    private final SysUserService sysUserService;
    private final VehicleInfoService vehicleInfoService;

    /**
     * 获取当前登录用户所属部门及下级部门ID列表
     *
     * @param userId 用户ID
     * @return 部门ID列表
     */
    private List<Long> getUserDeptAndChildren(Long userId) {
        // 先查询用户直接关联的部门
        QueryWrapper userDeptQuery = QueryWrapper.create()
                .select(SYS_USER_DEPT.DEPT_ID)
                .from(SYS_USER_DEPT)
                .where(SYS_USER_DEPT.USER_ID.eq(userId));
        
        List<Row> userDepts = dataStatisticsMapper.selectRowsByQuery(userDeptQuery);
        if (userDepts.isEmpty()) {
            return new ArrayList<>();
        }
        
        // 提取用户直接关联的部门ID
        List<Long> directDeptIds = userDepts.stream()
                .map(row -> row.getLong("dept_id"))
                .collect(Collectors.toList());
        
        // 递归查询这些部门及其所有子部门（基于PID关系）
        Set<Long> allDeptIds = new HashSet<>(directDeptIds);
        findChildDepartments(directDeptIds, allDeptIds);
        
        return new ArrayList<>(allDeptIds);
    }
    
    /**
     * 递归查找子部门
     * 
     * @param parentDeptIds 父部门ID列表
     * @param result 结果集合
     */
    private void findChildDepartments(List<Long> parentDeptIds, Set<Long> result) {
        if (parentDeptIds.isEmpty()) {
            return;
        }
        
        // 查询父部门的直接子部门
        QueryWrapper childQuery = QueryWrapper.create()
                .select(SYS_DEPT.ID)
                .from(SYS_DEPT)
                .where(SYS_DEPT.PID.in(parentDeptIds))
                .and(SYS_DEPT.DEL_FLAG.eq("F"));
        
        List<Row> childDepts = dataStatisticsMapper.selectRowsByQuery(childQuery);
        if (childDepts.isEmpty()) {
            return;
        }
        
        // 提取子部门ID
        List<Long> childDeptIds = childDepts.stream()
                .map(row -> row.getLong("id"))
                .filter(Objects::nonNull)
                .collect(Collectors.toList());
        
        // 添加到结果中
        result.addAll(childDeptIds);
        
        // 递归查找子部门的子部门
        findChildDepartments(childDeptIds, result);
    }

    @Override
    public PageResult<DriverStatisticsVO> pageDrivers(DataStatisticsQueryDTO dto) {
        try {
            // 获取当前登录用户及其部门信息
            LoginUser loginUser = LoginUtils.getLoginUser();
            List<Long> deptIds = getUserDeptAndChildren(loginUser.getUserInfo().getId());
            
            // 构建查询时间条件
            LocalDateTime startTime = dto.getStartTime();
            LocalDateTime endTime = dto.getEndTime();
            
            // 如果指定了月份，使用月份作为查询条件
            if (dto.getMonth() != null && !dto.getMonth().isEmpty()) {
                YearMonth yearMonth = YearMonth.parse(dto.getMonth(), DateTimeFormatter.ofPattern("yyyy-MM"));
                startTime = yearMonth.atDay(1).atStartOfDay();
                endTime = yearMonth.atEndOfMonth().atTime(23, 59, 59);
            }
            // 如果没有指定时间范围或月份的查询条件，则默认查询所有时间的数据
            // 移除了默认为当前月份的逻辑
            
            // 创建用于统计司机工作记录数的子查询
            QueryWrapper workCountSubQuery = QueryWrapper.create()
                    .select(column("smfr.driver_id").as("driver_id"),
                           count(column("smfr.id")).as("work_count"))
                    .from("sys_monitor_file_record").as("smfr")
                    .where(column("smfr.del_flag").eq("F"))
                    .and(column("smfr.process_status").eq("1"))
                    .groupBy(column("smfr.driver_id"));
            
            // 构建查询条件 - 关联vehicle_info和vehicle_schedule表
            QueryWrapper queryWrapper = QueryWrapper.create()
                    .select(SYS_USER.ID.as("userId"),
                            SYS_USER.USERNAME.as("username"),
                            SYS_USER.NICKNAME.as("nickname"),
                            SYS_USER.PHONE.as("phone"),
                            SYS_DEPT.NAME.as("deptName"),
                            VEHICLE_INFO.ID.as("vehicleId"),
                            VEHICLE_INFO.VEHICLE_NUMBER.as("vehicleNumber"),
                            VEHICLE_INFO.TITLE.as("vehicleTitle"),
                            VEHICLE_INFO.DEPARTMENT_ID.as("departmentId"),
                            VEHICLE_INFO.SCHEDULE_ID.as("scheduleId"),
                            column("vs.schedule_type").as("scheduleType"),
                            column("vs.title").as("scheduleName"),
                            column("vs.time1").as("scheduleTime1"),
                            column("vs.time2").as("scheduleTime2"),
                            column("vs.time3").as("scheduleTime3"),
                            case_().when(VEHICLE_INFO.USER_ID1.eq(SYS_USER.ID)).then(1)
                                 .when(VEHICLE_INFO.USER_ID2.eq(SYS_USER.ID)).then(2)
                                 .when(VEHICLE_INFO.USER_ID3.eq(SYS_USER.ID)).then(3)
                                 .else_(0).end().as("driverPosition"),
                            column("wc.work_count").as("driverWorkTotal"))
                    .from(SYS_USER)
                    .leftJoin(SYS_USER_DEPT).on(SYS_USER.ID.eq(SYS_USER_DEPT.USER_ID))
                    .leftJoin(SYS_DEPT).on(SYS_USER_DEPT.DEPT_ID.eq(SYS_DEPT.ID))
                    .leftJoin(SYS_USER_ROLE).on(SYS_USER.ID.eq(SYS_USER_ROLE.USER_ID))
                    .leftJoin(VEHICLE_INFO).on(SYS_USER.ID.eq(VEHICLE_INFO.USER_ID1)
                                              .or(SYS_USER.ID.eq(VEHICLE_INFO.USER_ID2))
                                              .or(SYS_USER.ID.eq(VEHICLE_INFO.USER_ID3)))
                    .leftJoin("vehicle_schedule").as("vs").on(VEHICLE_INFO.SCHEDULE_ID.eq(column("vs.id")))
                    .leftJoin(workCountSubQuery).as("wc").on(SYS_USER.ID.eq(column("wc.driver_id")))
                    .where(SYS_USER.DEL_FLAG.eq("F"))
                    .and(SYS_USER_ROLE.ROLE_ID.eq(7L)) // 司机角色ID为7
                    .and(SYS_DEPT.ID.in(deptIds))
                    .and(VEHICLE_INFO.ID.isNotNull()); // 只查询有关联车辆的司机
            
            // 只有当时间范围被指定时才添加时间条件
            if (startTime != null && endTime != null) {
                queryWrapper.and(SYS_USER.CREATE_TIME.between(startTime, endTime));
            }
            
            // 执行分页查询
            Page<DriverStatisticsVO> page = dataStatisticsMapper.paginateAs(PageUtils.getPage(dto), queryWrapper, DriverStatisticsVO.class);
            return PageUtils.getPageResult(page);
        } finally {
            SimpleDataScopeHelper.clearDataScope();
        }
    }

    @Override
    public PageResult<VehicleStatisticsVO> pageExcavators(DataStatisticsQueryDTO dto) {
        return pageVehicles(dto, "1009001"); // 钩机车辆类型编码
    }

    @Override
    public PageResult<VehicleStatisticsVO> pageTransportVehicles(DataStatisticsQueryDTO dto) {
        return pageVehicles(dto, "1009002"); // 运输车车辆类型编码
    }

    /**
     * 分页查询车辆数据
     *
     * @param dto 查询参数
     * @param vehicleTypeCd 车辆类型编码
     * @return 车辆统计数据分页结果
     */
    private PageResult<VehicleStatisticsVO> pageVehicles(DataStatisticsQueryDTO dto, String vehicleTypeCd) {
        try {
            // 获取当前登录用户及其部门信息
            LoginUser loginUser = LoginUtils.getLoginUser();
            List<Long> deptIds = getUserDeptAndChildren(loginUser.getUserInfo().getId());
            
            // 构建查询时间条件
            LocalDateTime startTime = dto.getStartTime();
            LocalDateTime endTime = dto.getEndTime();
            
            // 如果指定了月份，使用月份作为查询条件
            if (dto.getMonth() != null && !dto.getMonth().isEmpty()) {
                YearMonth yearMonth = YearMonth.parse(dto.getMonth(), DateTimeFormatter.ofPattern("yyyy-MM"));
                startTime = yearMonth.atDay(1).atStartOfDay();
                endTime = yearMonth.atEndOfMonth().atTime(23, 59, 59);
            }
            // 如果没有指定时间范围或月份的查询条件，则默认查询所有时间的数据
            // 移除了默认为当前月份的逻辑
            
            // 创建用于统计车辆工作记录数的子查询
            QueryWrapper workCountSubQuery = QueryWrapper.create()
                    .select(column("smfr.car_id").as("car_id"),
                           count(column("smfr.id")).as("work_count"))
                    .from("sys_monitor_file_record").as("smfr")
                    .where(column("smfr.del_flag").eq("F"))
                    .and(column("smfr.process_status").eq("1"))
                    .groupBy(column("smfr.car_id"));
            
            // 构建查询条件
            QueryWrapper queryWrapper = QueryWrapper.create()
                    .select(VEHICLE_INFO.ID.as("vehicleId"),
                            VEHICLE_INFO.VEHICLE_NUMBER.as("vehicleNumber"),
                            VEHICLE_INFO.TITLE.as("title"),
                            VEHICLE_INFO.VEHICLE_TYPE_CD.as("vehicleTypeCd"),
                            VEHICLE_INFO.USER_ID1.as("userId1"),
                            VEHICLE_INFO.USER_ID2.as("userId2"),
                            VEHICLE_INFO.USER_ID3.as("userId3"),
                            SYS_DEPT.NAME.as("deptName"),
                            SYS_DEPT.ID.as("departmentId"),
                            column("su1.username").as("driverName1"),
                            column("su2.username").as("driverName2"),
                            column("su3.username").as("driverName3"),
                            column("wc.work_count").as("vehicleWorkTotal"))
                    .from(VEHICLE_INFO)
                    .leftJoin(SYS_DEPT).on(VEHICLE_INFO.DEPARTMENT_ID.eq(SYS_DEPT.ID))
                    .leftJoin(SYS_USER).as("su1").on(VEHICLE_INFO.USER_ID1.eq(column("su1.id")))
                    .leftJoin(SYS_USER).as("su2").on(VEHICLE_INFO.USER_ID2.eq(column("su2.id")))
                    .leftJoin(SYS_USER).as("su3").on(VEHICLE_INFO.USER_ID3.eq(column("su3.id")))
                    .leftJoin(workCountSubQuery).as("wc").on(VEHICLE_INFO.ID.eq(column("wc.car_id")))
                    .where(VEHICLE_INFO.DEL_FLAG.eq("F"))
                    .and(VEHICLE_INFO.VEHICLE_TYPE_CD.eq(vehicleTypeCd))
                    .and(SYS_DEPT.ID.in(deptIds));
            
            // 只有当时间范围被指定时才添加时间条件
            if (startTime != null && endTime != null) {
                queryWrapper.and(VEHICLE_INFO.CREATE_TIME.between(startTime, endTime));
            }
            
            // 执行分页查询
            Page<VehicleStatisticsVO> page = dataStatisticsMapper.paginateAs(PageUtils.getPage(dto), queryWrapper, VehicleStatisticsVO.class);
            return PageUtils.getPageResult(page);
        } finally {
            SimpleDataScopeHelper.clearDataScope();
        }
    }

    @Override
    public Map<String, Long> getStatisticsCount(String month, LocalDateTime startTime, LocalDateTime endTime) {
        Map<String, Long> result = new HashMap<>();
        
        try {
            // 获取当前登录用户及其部门信息
            LoginUser loginUser = LoginUtils.getLoginUser();
            List<Long> deptIds = getUserDeptAndChildren(loginUser.getUserInfo().getId());
            
            // 构建查询时间条件
            LocalDateTime start = startTime;
            LocalDateTime end = endTime;
            
            // 如果指定了月份，使用月份作为查询条件
            if (month != null && !month.isEmpty()) {
                YearMonth yearMonth = YearMonth.parse(month, DateTimeFormatter.ofPattern("yyyy-MM"));
                start = yearMonth.atDay(1).atStartOfDay();
                end = yearMonth.atEndOfMonth().atTime(23, 59, 59);
            }
            // 如果没有指定时间范围或月份的查询条件，则默认查询所有时间的数据
            // 移除了默认为当前月份的逻辑
            
            // 统计司机数量
            QueryWrapper driverQuery = QueryWrapper.create()
                    .select(count(SYS_USER.ID).as("count"))
                    .from(SYS_USER)
                    .leftJoin(SYS_USER_DEPT).on(SYS_USER.ID.eq(SYS_USER_DEPT.USER_ID))
                    .leftJoin(SYS_USER_ROLE).on(SYS_USER.ID.eq(SYS_USER_ROLE.USER_ID))
                    .where(SYS_USER.DEL_FLAG.eq("F"))
                    .and(SYS_USER_ROLE.ROLE_ID.eq(7L)) // 司机角色ID为7
                    .and(SYS_USER_DEPT.DEPT_ID.in(deptIds));
            
            // 只有当时间范围被指定时才添加时间条件
            if (start != null && end != null) {
                driverQuery.and(SYS_USER.CREATE_TIME.between(start, end));
            }
            
            Long driverCount = dataStatisticsMapper.selectObjectByQueryAs(driverQuery, Long.class);
            result.put("driverCount", driverCount != null ? driverCount : 0L);
            
            // 统计钩机车辆数量
            QueryWrapper excavatorQuery = QueryWrapper.create()
                    .select(count(VEHICLE_INFO.ID).as("count"))
                    .from(VEHICLE_INFO)
                    .leftJoin(SYS_DEPT).on(VEHICLE_INFO.DEPARTMENT_ID.eq(SYS_DEPT.ID))
                    .where(VEHICLE_INFO.DEL_FLAG.eq("F"))
                    .and(VEHICLE_INFO.VEHICLE_TYPE_CD.eq("1009001")) // 钩机车辆类型编码
                    .and(SYS_DEPT.ID.in(deptIds));
            
            // 只有当时间范围被指定时才添加时间条件
            if (start != null && end != null) {
                excavatorQuery.and(VEHICLE_INFO.CREATE_TIME.between(start, end));
            }
            
            Long excavatorCount = dataStatisticsMapper.selectObjectByQueryAs(excavatorQuery, Long.class);
            result.put("excavatorCount", excavatorCount != null ? excavatorCount : 0L);
            
            // 统计运输车车辆数量
            QueryWrapper transportQuery = QueryWrapper.create()
                    .select(count(VEHICLE_INFO.ID).as("count"))
                    .from(VEHICLE_INFO)
                    .leftJoin(SYS_DEPT).on(VEHICLE_INFO.DEPARTMENT_ID.eq(SYS_DEPT.ID))
                    .where(VEHICLE_INFO.DEL_FLAG.eq("F"))
                    .and(VEHICLE_INFO.VEHICLE_TYPE_CD.eq("1009002")) // 运输车车辆类型编码
                    .and(SYS_DEPT.ID.in(deptIds));
            
            // 只有当时间范围被指定时才添加时间条件
            if (start != null && end != null) {
                transportQuery.and(VEHICLE_INFO.CREATE_TIME.between(start, end));
            }
            
            Long transportCount = dataStatisticsMapper.selectObjectByQueryAs(transportQuery, Long.class);
            result.put("transportCount", transportCount != null ? transportCount : 0L);
            
            // 统计作业数据数量
            QueryWrapper workDataQuery = QueryWrapper.create()
                    .select(count(column("r.id")).as("count"))
                    .from("sys_monitor_file_record").as("r")
                    .leftJoin("dtu_info").as("dtu").on("r.file_tag = dtu.dtu_number")
                    .leftJoin("vehicle_info").as("v").on("dtu.vehicle_id = v.id")
                    .leftJoin(SYS_DEPT).as("d").on(column("v.department_id").eq(SYS_DEPT.ID))
                    .where("r.del_flag = 'F'")
                    .and("r.process_status = '1'") // 只查询成功处理的记录
                    .and(SYS_DEPT.ID.in(deptIds));
            
            // 只有当时间范围被指定时才添加时间条件
            if (start != null && end != null) {
                workDataQuery.and(column("r.create_time").between(start, end));
            }
            
            Long workDataCount = dataStatisticsMapper.selectObjectByQueryAs(workDataQuery, Long.class);
            result.put("workDataCount", workDataCount != null ? workDataCount : 0L);
            
            return result;
        } finally {
            SimpleDataScopeHelper.clearDataScope();
        }
    }

    @Override
    public PageResult<WorkDataStatisticsVO> pageWorkData(DataStatisticsQueryDTO dto) {
        try {
            // 获取当前登录用户及其部门信息
            LoginUser loginUser = LoginUtils.getLoginUser();
            List<Long> deptIds = getUserDeptAndChildren(loginUser.getUserInfo().getId());
            
            // 构建查询时间条件
            LocalDateTime startTime = dto.getStartTime();
            LocalDateTime endTime = dto.getEndTime();
            
            // 如果指定了月份，使用月份作为查询条件
            if (dto.getMonth() != null && !dto.getMonth().isEmpty()) {
                YearMonth yearMonth = YearMonth.parse(dto.getMonth(), DateTimeFormatter.ofPattern("yyyy-MM"));
                startTime = yearMonth.atDay(1).atStartOfDay();
                endTime = yearMonth.atEndOfMonth().atTime(23, 59, 59);
            }
            // 如果没有指定时间范围或月份的查询条件，则默认查询所有时间的数据
            // 移除了默认为当前月份的逻辑
            
            // 构建查询条件
            // 1. 查询sys_monitor_file_record的数据
            // 2. 关联file_tag作为设备编号去关联dtu_info的dtu_number
            // 3. 再根据vehicle_id关联vehicle_info的department_id
            // 4. 查询department_id在当前登录用户的部门和下级部门的数据
            QueryWrapper queryWrapper = QueryWrapper.create()
                    .select("r.id", "r.monitor_id", //"m.description as monitor_description",
                            "r.original_path", "r.original_filename", "r.new_filename", "r.storage_path", 
                            "r.file_size", "r.file_type", "r.file_url", "r.process_status",
                            "CASE r.process_status WHEN '1' THEN '成功' WHEN '0' THEN '失败' ELSE '未知' END as process_status_display",
                            "r.error_message", "r.process_time", "r.file_md5", "r.file_tag", 
                            "r.car_number", "r.car_number_res", "r.create_time",
                            "d.name as deptName",
                            "su.username as driverName","su.id as driverId")
                    .from("sys_monitor_file_record").as("r")
                    //.leftJoin("sys_file_monitor").as("m").on("r.monitor_id = m.id")
                    .leftJoin("dtu_info").as("dtu").on("r.file_tag = dtu.dtu_number")
                    .leftJoin("vehicle_info").as("v").on("dtu.vehicle_id = v.id")
                    .leftJoin("sys_user").as("su").on("r.driver_id = su.id")
                    .leftJoin(SYS_DEPT).as("d").on(column("v.department_id").eq(SYS_DEPT.ID))
                    .where("r.del_flag = 'F'")
                    .and("r.process_status = '1'") // 只查询成功处理的记录
                    .and(SYS_DEPT.ID.in(deptIds));
            
            // 只有当时间范围被指定时才添加时间条件
            if (startTime != null && endTime != null) {
                queryWrapper.and(column("r.create_time").between(startTime, endTime));
            }
            
            // 执行分页查询
            Page<WorkDataStatisticsVO> page = dataStatisticsMapper.paginateAs(PageUtils.getPage(dto), queryWrapper, WorkDataStatisticsVO.class);
            return PageUtils.getPageResult(page);
        } finally {
            SimpleDataScopeHelper.clearDataScope();
        }
    }

    @Override
    public PageResult<WorkDataStatisticsVO> pageWorkDataByDriverId(DataStatisticsQueryDTO dto, Long driverId) {
        try {
            // 获取当前登录用户及其部门信息
            LoginUser loginUser = LoginUtils.getLoginUser();
            List<Long> deptIds = getUserDeptAndChildren(loginUser.getUserInfo().getId());
            
            // 构建查询时间条件
            LocalDateTime startTime = dto.getStartTime();
            LocalDateTime endTime = dto.getEndTime();
            
            // 如果指定了月份，使用月份作为查询条件
            if (dto.getMonth() != null && !dto.getMonth().isEmpty()) {
                YearMonth yearMonth = YearMonth.parse(dto.getMonth(), DateTimeFormatter.ofPattern("yyyy-MM"));
                startTime = yearMonth.atDay(1).atStartOfDay();
                endTime = yearMonth.atEndOfMonth().atTime(23, 59, 59);
            }
            // 如果没有指定时间范围或月份的查询条件，则默认查询所有时间的数据
            // 移除了默认为当前月份的逻辑
            
            // 构建查询条件
            // 1. 查询sys_monitor_file_record的数据
            // 2. 关联file_tag作为设备编号去关联dtu_info的dtu_number
            // 3. 再根据vehicle_id关联vehicle_info的department_id
            // 4. 查询department_id在当前登录用户的部门和下级部门的数据
            // 5. 根据指定的driverId过滤
            QueryWrapper queryWrapper = QueryWrapper.create()
                    .select("r.id", "r.monitor_id", //"m.description as monitor_description",
                            "r.original_path", "r.original_filename", "r.new_filename", "r.storage_path", 
                            "r.file_size", "r.file_type", "r.file_url", "r.process_status",
                            "CASE r.process_status WHEN '1' THEN '成功' WHEN '0' THEN '失败' ELSE '未知' END as process_status_display",
                            "r.error_message", "r.process_time", "r.file_md5", "r.file_tag", 
                            "r.car_number", "r.car_number_res", "r.create_time",
                            "DATE(r.create_time) as createDate",  // 添加日期字段用于排序
                            "d.name as deptName",
                            "su.username as driverName","su.id as driverId")
                    .from("sys_monitor_file_record").as("r")
                    //.leftJoin("sys_file_monitor").as("m").on("r.monitor_id = m.id")
                    .leftJoin("dtu_info").as("dtu").on("r.file_tag = dtu.dtu_number")
                    .leftJoin("vehicle_info").as("v").on("dtu.vehicle_id = v.id")
                    .leftJoin("sys_user").as("su").on("r.driver_id = su.id")
                    .leftJoin(SYS_DEPT).as("d").on(column("v.department_id").eq(SYS_DEPT.ID))
                    .where("r.del_flag = 'F'")
                    .and("r.process_status = '1'") // 只查询成功处理的记录
                    .and("r.driver_id = ?", driverId) // 根据指定的司机ID过滤
                    .and(SYS_DEPT.ID.in(deptIds));
            
            // 只有当时间范围被指定时才添加时间条件
            if (startTime != null && endTime != null) {
                queryWrapper.and(column("r.create_time").between(startTime, endTime));
            }
            
            // 按日期排序（最新的日期在前）
            queryWrapper.orderBy(column("createDate").desc(), column("r.create_time").desc());
            
            // 执行分页查询
            Page<WorkDataStatisticsVO> page = dataStatisticsMapper.paginateAs(PageUtils.getPage(dto), queryWrapper, WorkDataStatisticsVO.class);
            return PageUtils.getPageResult(page);
        } finally {
            SimpleDataScopeHelper.clearDataScope();
        }
    }

    @Override
    public PageResult<WorkDataGroupedByDateVO> pageWorkDataByDriverIdGroupedByDate(DataStatisticsQueryDTO dto, Long driverId) {
        try {
            // 获取当前登录用户及其部门信息
            LoginUser loginUser = LoginUtils.getLoginUser();
            List<Long> deptIds = getUserDeptAndChildren(loginUser.getUserInfo().getId());
            
            // 构建查询时间条件
            LocalDateTime startTime = dto.getStartTime();
            LocalDateTime endTime = dto.getEndTime();
            
            // 如果指定了月份，使用月份作为查询条件
            if (dto.getMonth() != null && !dto.getMonth().isEmpty()) {
                YearMonth yearMonth = YearMonth.parse(dto.getMonth(), DateTimeFormatter.ofPattern("yyyy-MM"));
                startTime = yearMonth.atDay(1).atStartOfDay();
                endTime = yearMonth.atEndOfMonth().atTime(23, 59, 59);
            }
            // 如果没有指定时间范围或月份的查询条件，则默认查询所有时间的数据
            // 移除了默认为当前月份的逻辑
            
            // 先查询所有符合条件的记录
            QueryWrapper queryWrapper = QueryWrapper.create()
                    .select("r.id", "r.monitor_id", //"m.description as monitor_description",
                            "r.original_path", "r.original_filename", "r.new_filename", "r.storage_path", 
                            "r.file_size", "r.file_type", "r.file_url", "r.process_status",
                            "CASE r.process_status WHEN '1' THEN '成功' WHEN '0' THEN '失败' ELSE '未知' END as process_status_display",
                            "r.error_message", "r.process_time", "r.file_md5", "r.file_tag", 
                            "r.car_number", "r.car_number_res", "r.create_time",
                            "DATE(r.create_time) as createDate",  // 添加日期字段用于分组
                            "d.name as deptName",
                            "su.username as driverName","su.id as driverId")
                    .from("sys_monitor_file_record").as("r")
                    //.leftJoin("sys_file_monitor").as("m").on("r.monitor_id = m.id")
                    .leftJoin("dtu_info").as("dtu").on("r.file_tag = dtu.dtu_number")
                    .leftJoin("vehicle_info").as("v").on("dtu.vehicle_id = v.id")
                    .leftJoin("sys_user").as("su").on("r.driver_id = su.id")
                    .leftJoin(SYS_DEPT).as("d").on(column("v.department_id").eq(SYS_DEPT.ID))
                    .where("r.del_flag = 'F'")
                    .and("r.process_status = '1'") // 只查询成功处理的记录
                    .and("r.driver_id = ?", driverId) // 根据指定的司机ID过滤
                    .and(SYS_DEPT.ID.in(deptIds));
            
            // 只有当时间范围被指定时才添加时间条件
            if (startTime != null && endTime != null) {
                queryWrapper.and(column("r.create_time").between(startTime, endTime));
            }
            
            // 按日期和创建时间排序
            queryWrapper.orderBy(column("createDate").desc(), column("r.create_time").desc());
            
            // 执行查询获取所有记录
            List<WorkDataStatisticsVO> allRecords = dataStatisticsMapper.selectListByQueryAs(queryWrapper, WorkDataStatisticsVO.class);
            
            // 按日期分组
            Map<LocalDate, List<WorkDataStatisticsVO>> groupedByDate = allRecords.stream()
                    .collect(Collectors.groupingBy(WorkDataStatisticsVO::getCreateDate));
            
            // 转换为分页结果
            List<WorkDataGroupedByDateVO> result = groupedByDate.entrySet().stream()
                    .map(entry -> {
                        WorkDataGroupedByDateVO groupedVO = new WorkDataGroupedByDateVO();
                        groupedVO.setDate(entry.getKey());
                        groupedVO.setRecords(entry.getValue());
                        return groupedVO;
                    })
                    .collect(Collectors.toList());
            
            // 应用分页
            Page<WorkDataGroupedByDateVO> page = new Page<>(PageUtils.getPage(dto).getPageNumber(), PageUtils.getPage(dto).getPageSize());
            page.setTotalRow(result.size());
            
            // 手动分页
            int start = (int) ((page.getPageNumber() - 1) * page.getPageSize());
            int end = Math.min(start + (int) page.getPageSize(), result.size());
            page.setRecords(result.subList(start, end));
            
            return PageUtils.getPageResult(page);
        } finally {
            SimpleDataScopeHelper.clearDataScope();
        }
    }

    @Override
    public PageResult<WorkDataGroupedByDateVO> pageWorkDataByVehicleIdGroupedByDate(DataStatisticsQueryDTO dto, Long vehicleId) {
        try {
            // 获取当前登录用户及其部门信息
            LoginUser loginUser = LoginUtils.getLoginUser();
            List<Long> deptIds = getUserDeptAndChildren(loginUser.getUserInfo().getId());
            
            // 构建查询时间条件
            LocalDateTime startTime = dto.getStartTime();
            LocalDateTime endTime = dto.getEndTime();
            
            // 如果指定了月份，使用月份作为查询条件
            if (dto.getMonth() != null && !dto.getMonth().isEmpty()) {
                YearMonth yearMonth = YearMonth.parse(dto.getMonth(), DateTimeFormatter.ofPattern("yyyy-MM"));
                startTime = yearMonth.atDay(1).atStartOfDay();
                endTime = yearMonth.atEndOfMonth().atTime(23, 59, 59);
            }
            // 如果没有指定时间范围或月份的查询条件，则默认查询所有时间的数据
            // 移除了默认为当前月份的逻辑
            
            // 先查询所有符合条件的记录
            QueryWrapper queryWrapper = QueryWrapper.create()
                    .select("r.id", "r.monitor_id", //"m.description as monitor_description",
                            "r.original_path", "r.original_filename", "r.new_filename", "r.storage_path", 
                            "r.file_size", "r.file_type", "r.file_url", "r.process_status",
                            "CASE r.process_status WHEN '1' THEN '成功' WHEN '0' THEN '失败' ELSE '未知' END as process_status_display",
                            "r.error_message", "r.process_time", "r.file_md5", "r.file_tag", 
                            "r.car_number", "r.car_number_res", "r.create_time",
                            "DATE(r.create_time) as createDate",  // 添加日期字段用于分组
                            "d.name as deptName",
                            "su.username as driverName","su.id as driverId")
                    .from("sys_monitor_file_record").as("r")
                    //.leftJoin("sys_file_monitor").as("m").on("r.monitor_id = m.id")
                    .leftJoin("dtu_info").as("dtu").on("r.file_tag = dtu.dtu_number")
                    .leftJoin("vehicle_info").as("v").on("dtu.vehicle_id = v.id")
                    .leftJoin("sys_user").as("su").on("r.driver_id = su.id")
                    .leftJoin(SYS_DEPT).as("d").on(column("v.department_id").eq(SYS_DEPT.ID))
                    .where("r.del_flag = 'F'")
                    .and("r.process_status = '1'") // 只查询成功处理的记录
                    .and("r.car_id = ?", vehicleId) // 根据指定的车辆ID过滤
                    .and(SYS_DEPT.ID.in(deptIds));
            
            // 只有当时间范围被指定时才添加时间条件
            if (startTime != null && endTime != null) {
                queryWrapper.and(column("r.create_time").between(startTime, endTime));
            }
            
            // 按日期和创建时间排序
            queryWrapper.orderBy(column("createDate").desc(), column("r.create_time").desc());
            
            // 执行查询获取所有记录
            List<WorkDataStatisticsVO> allRecords = dataStatisticsMapper.selectListByQueryAs(queryWrapper, WorkDataStatisticsVO.class);
            
            // 按日期分组
            Map<LocalDate, List<WorkDataStatisticsVO>> groupedByDate = allRecords.stream()
                    .collect(Collectors.groupingBy(WorkDataStatisticsVO::getCreateDate));
            
            // 转换为分页结果
            List<WorkDataGroupedByDateVO> result = groupedByDate.entrySet().stream()
                    .map(entry -> {
                        WorkDataGroupedByDateVO groupedVO = new WorkDataGroupedByDateVO();
                        groupedVO.setDate(entry.getKey());
                        groupedVO.setRecords(entry.getValue());
                        return groupedVO;
                    })
                    .collect(Collectors.toList());
            
            // 应用分页
            Page<WorkDataGroupedByDateVO> page = new Page<>(PageUtils.getPage(dto).getPageNumber(), PageUtils.getPage(dto).getPageSize());
            page.setTotalRow(result.size());
            
            // 手动分页
            int start = (int) ((page.getPageNumber() - 1) * page.getPageSize());
            int end = Math.min(start + (int) page.getPageSize(), result.size());
            page.setRecords(result.subList(start, end));
            
            return PageUtils.getPageResult(page);
        } finally {
            SimpleDataScopeHelper.clearDataScope();
        }
    }
}